package Dao;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import valueObject.Card;
import valueObject.Coach;
import valueObject.Course;
import valueObject.Customer;
import valueObject.Schedule;

import jdbcConn.MySqlConnect;


public class DealUtil {
	/**
	 * 新增客户 
	 * @param Customer 客户对象
	 * @return
	 */
	public boolean addCustomer(Customer cust) {
		boolean isSuccess = false;
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String custname = cust.getCustName();
			String address = cust.getAddress();
			String job = cust.getJob();
			String sql = "INSERT INTO CUSTOMER (CUSTOMER_NAME, ADDRESS, JOB, STATE) VALUES ('" + custname + "','" + address + "','" + job + "','1')";
			isSuccess = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		return isSuccess;
	}
	/**
	 * 新增会员
	 * @param Card 会员对象
	 * @return
	 */
	public boolean addCard(Card card) {
		boolean isSuccess = false;
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			int custId = card.getCustomerId();
			String cardType = card.getCardType();
			int useTimes = card.getUseTime();
			Date date = new Date();
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String time = format.format(date);
			String sql = "INSERT INTO CARD (CUSTOMER_ID, CARD_TYPE, CREATE_DATE, USE_TIME,STATE) VALUES ('" + custId + "','" + cardType + "','" + time + "','"+useTimes+"','1')";
			isSuccess = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		return isSuccess;
	}

	/**
	 * 根据客户姓名查询客户
	 * @param name 客户姓名
	 * @return Customer[] 客户对象
	 */
	public Customer[] qryCust(String name) {
//		Customer cust = new Customer();
		ArrayList<Customer> cust = new ArrayList<Customer>();
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		String sql = "SELECT * FROM CUSTOMER WHERE CUSTOMER_NAME=" + "'"+name +"'";
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCustId(Integer.parseInt(rs.getString("CUSTOMER_ID")));
				customer.setCustName(rs.getString("CUSTOMER_NAME"));
				cust.add(customer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		Customer[] custs = (Customer[]) cust.toArray(new Customer[0]);
		return custs;
	}
	/**
	 * 根据表名和自增列名得到当前值
	 * @param tableName 表名
	 * @param colName 列名
	 * @return nowId 当前值
	 */
	public int getNowId(String tableName, String colName) {
		int nowId = -1;
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		String sql = "SELECT MAX(" +colName+ ") FROM "+ tableName;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				nowId = rs.getByte(1);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			MySqlConnect.close(conn);
		}
		return nowId;
		
	}
	
	/**
	 * 新增课程
	 * @param Course 课程对象
	 * @return
	 */
	public boolean addCourse(Course course) {
		boolean isSuccess = false;
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String courseName = course.getCourseName();
			Date date = new Date();
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String createDate = format.format(date);
			String sql = "INSERT INTO COURSE (COURSE_NAME, STUDENT_NUMBER, STATE, CREATE_DATE) VALUES ('" + courseName + "', '30', 'U','"+createDate+"')";
			isSuccess = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		return isSuccess;
	}
	
	/**
	 * 新增教练
	 * @param Coach 教练对象
	 * @return
	 */
	public boolean addCoach(Coach coach) {
		boolean isSuccess = false;
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String coachName = coach.getCoachName();
			String family = coach.getFamily();
			int age = coach.getAge();
			String advantage = coach.getAdvantage();
			Date date = new Date();
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String createDate = format.format(date);
			String sql = "INSERT INTO COACH (COACH_NAME, FAMILY, AGE, ADVANTAGE, CREATE_DATE,STATE) VALUES ('" + coachName + "', '"+family+"', '"+age+"', '"+advantage+"','"+createDate+"','U')";
			isSuccess = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		return isSuccess;
	}
	
	
	/**
	 * 新增排课信息
	 * @param Coach 排课信息
	 * @return
	 */
	public boolean addSchedule(Schedule schedule) {
		boolean isSuccess = false;
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			int courseId = schedule.getCourseId();
			int coachId = schedule.getCoachId();
			String time = schedule.getTime();
			String sql = "INSERT INTO SCHEDULE (COURSE_ID, COACH_ID, TIME,STATE) VALUES ('" + courseId + "', '"+coachId+"', '"+time+"', 'U')";
			isSuccess = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		return isSuccess;
	}
	
	/**
	 * 查询排课信息
	 * @param time 上课时间
	 * @return Schedule 排课信息
	 */
	public Schedule qrySchedule(String time) {
		Schedule schedule =new Schedule();
		Connection conn = MySqlConnect.Connect();
		Statement stmt;
		String sql = "SELECT * FROM SCHEDULE WHERE TIME=" + "'"+time +"'";
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()) {
				schedule.setCourseId(Integer.parseInt(rs.getString("COURSE_ID")));
				schedule.setCoachId(Integer.parseInt(rs.getString("COACH_ID")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			MySqlConnect.close(conn);
		}
		return schedule;
	}
}
